package com.kvn.backdoor.common;

import com.kvn.backdoor.entity.DbConfig;
import org.springframework.util.StringUtils;

import java.sql.*;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;

/**
 * Created by wangzhiyuan on 2018/8/17
 */
public class DbUtil {

    private DbConfig dbConfig;

    public static DbUtil use(DbConfig dbConfig) {
        DbUtil dbUtil = new DbUtil();
        dbUtil.dbConfig = dbConfig;
        return dbUtil;
    }

    /**
     * 批量执行 sql ，在一个事务中
     * @param sqls
     * @return
     */
    public LinkedHashMap<String, Object> executeBatch(String... sqls){
        LinkedHashMap<String, Object> resultMap = new LinkedHashMap<>();

        PreparedStatement ps = null;
        ResultSet rs = null;
        try(Connection con = DriverManager.getConnection(dbConfig.getUrl(), dbConfig.getUserName(), dbConfig.getPassword())) {
            con.setAutoCommit(false);
            for (int i = 0; i < sqls.length; i++) {
                String sql = sqls[i].trim();
                if (StringUtils.isEmpty(sql)) {
                    continue;
                }
                ps = con.prepareStatement(sql);
                boolean isSelect = isSelect(sql);
                String msg = isSelect ? " 【查询结果】" : " 【更新结果】";
                msg = "第" + (i + 1) + "项：" + sql + msg;
                if (isSelect) {
                    rs = ps.executeQuery();
                    resultMap.put(msg, handleResultSet(rs));
                    continue;
                }

                resultMap.put(msg, ps.executeUpdate());
            }
            con.commit(); // 手动提交事务
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            try {
                if (rs != null) {
                    rs.close();
                }
                if (ps != null) {
                    ps.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return resultMap;
    }

    public Object execute(String sql) {
        boolean isSelect = isSelect(sql);
        if (isSelect) {
            try (Connection con = DriverManager.getConnection(dbConfig.getUrl(), dbConfig.getUserName(), dbConfig.getPassword()); PreparedStatement ps = con.prepareStatement(sql); ResultSet rs = ps.executeQuery()) {
                return handleResultSet(rs);
            } catch (SQLException e) {
                throw new RuntimeException(sql, e);
            }
        }

        try (Connection con = DriverManager.getConnection(dbConfig.getUrl(), dbConfig.getUserName(), dbConfig.getPassword()); PreparedStatement ps = con.prepareStatement(sql)) {
            return ps.executeUpdate();
        } catch (SQLException e) {
            throw new RuntimeException(sql, e);
        }

    }

    public static boolean isSelect(String sql) {
        sql = sql.trim();
        return sql.startsWith("select");
    }

    private Object handleResultSet(ResultSet rs) {
        List resultList = new ArrayList();
        try {
            int columnCount = rs.getMetaData().getColumnCount();
            while (rs.next()) {
                Object[] recordColumnValues = new Object[columnCount];
                for (int i = 0; i < columnCount; i++) {
                    recordColumnValues[i] = rs.getObject(i + 1);
                }
                resultList.add(recordColumnValues);
            }
            return resultList;
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
    }

}
